import sqlite3


def execute_sql(sql):
    conn = get_conn()
    cursor = conn.cursor()
    rs = cursor.execute(sql)
    for item in rs:
        print(item)


def select_python_course():
    """
    查询出都有哪些同学学习了Python课程，要求查询出同学的姓名、课程名称
    :return:
    """
    sql = "select s.student_name,c.course_name from student_course sc,course c ,student s where sc.course_no = c.course_no and sc.student_no = s.student_no and c.course_name = '{}'".format(
        "Python")
    execute_sql(sql)


def select_machine_learn():
    """
    查询出1年1班，都有哪些同学学习了机器学习课程，要求查询出同学的姓名、课程名称、班级名称、年级名称
    :return:
    """
    sql = "select s.student_name,c.course_name,class.class_name,g.grade_name from student_course sc,course c ,student s,class,grade g  where sc.course_no = c.course_no and sc.student_no = s.student_no and s.class_no = class.class_no and  class.grade_no = g.grade_no and c.course_name = '{}' and class.class_no = {}".format(
        "机器学习", 1)
    execute_sql(sql)


def python_and_analyze():
    """
    哪些同学同时报了Python课程和数据分析课程
    :return:
    """
    sql = "select distinct sc1.student_no  from student_course sc1,student_course sc2 where sc1.student_no = sc2.student_no and sc1.course_no = 7 and sc2.course_no = 12 order by sc1.student_no asc"
    execute_sql(sql)


def analyze_score():
    """
    查询出2年级，所有考试未及格的同学
    :return:
    """
    sql = "select s.student_name,c.course_name,sc.score from student_course sc,course c ,student s where sc.course_no = c.course_no and sc.student_no = s.student_no and c.course_name = '数据分析' and  sc.score >= 60 order by sc.score desc"
    execute_sql(sql)


def under_stu():
    sql = "select distinct s.student_no,student_name from student_course tc,student s ,class c where tc.student_no = s.student_no and s.class_no = c.class_no and c.grade_no = 2 and tc.score < 60"
    execute_sql(sql)


def get_conn():
    return sqlite3.connect("schools.db")


if __name__ == "__main__":
    # select_python_course()
    # select_machine_learn()
    # python_and_analyze()
    # analyze_score()
    under_stu()